Genesis Management Services Pty Ltd gmservices.com.au
genesis
SERVICES
EXPERIENCE
UNIT 15 SPREAD-SHEET BUDGETING – PART 1
The following financial statements need to be budgeted:
Profit & Loss Statement
Cash Budget
Funds Budget
Balance Sheet Budget
Once these financial statements have been prepared we can then complete a ratio analysis to ensure that the objectives are achievable. It is important to be realistic in this task. For example, we might decide an ROI objective of 30% per annum is desirable yet we can only produce a budget for 15%. Something has to give! Do we try and stretch the budget to such an extent that it might become unachievable or do we modify our budget expectations? The best answer is to set objectives that are realistic but will stretch management performance. If over several budget periods the expected performance does not look like coming up to standard quickly enough, we should modify our plans accordingly.
Profit & Loss Statement Budget
The best starting point for budgeting is sales forecasting. We should prepare a sales budget using past figures as a guide. We should think very logically about potential sales in each area of the business and prepare separate sales budgets for key customers, key products and key geographical areas if necessary. We might expand these into greater detail for the sales consultant concerned. It is best if each sales consultant has a budget to achieve so that we can “manage by objectives”.
The sales budget figures must follow preparation of the marketing plan. Each of the figures therefore represents the consequence of the various marketing mix inputs. In other words, each figure assumes some fundamental logic concerning pricing, commission policy, product mix, promotion, sales training, etc.
Cash Budgeting
The best way to remember how to do a cash flow is to recognise that total inflows refer to all the items you would expect in the future to go through your cash receipts book. Conversely total outflows would be all the items that you would expect to go through your cash payments book.
All we are doing with the cash flow budget is predicting what our bank balance will be.
At the implementation stage, the best kind of cash flow budget is one that will accommodate columns for budget, actual and variation. You can even be more sophisticated and include percentage columns.
Budgeted Funds Statements
When we come to do a budgeted funds statement we can approach the task in two ways.
Firstly, we can list the total funds we think we can raise from all sources and then decide on a range of investments to spend this money.
Secondly, and this is the more normal approach, we can draw up a “shopping list” of prospective investments, screening them carefully in terms of their impact on liquidity, profitability and security and then decide what total funds are required to implement the investment programme. We can then set out to identify all the possible sources of funds to see if we can fund the total investment programme.
In the final analysis we will have to compromise on either uses of funds or sources of funds or both.
Budgeted Balance Sheet
Once the Funds Statement is prepared we can calculate the Balance sheet very simply just by adding the sources and use figures to the last Balance Sheet figures. This is the reverse of what we have done in previous sections.
try preparing a budgeted balance sheet for Tourism Development Corporation. Cover the 1988 columns for the balance sheet and try and construct a 1988 balance sheet by adding the funds statement figures to the 1987 balance sheet figures.
Introductory Exercise. Refer now to the following spreadsheet for “Basic Enterprises - Solution”.
A
B
C
D
E
F
2
BASIC ENTERPRISES - SOLUTION
3
4
PROFIT AND LOSS
JULY
AUG
SEPT
TOTAL
5
6
INCOME
50,000
60,000
70,000
180,000
7
LESS VARIABLE COSTS
8
Material
9
Stock at beginning
-
15,000
17,000
-
10
Purchases
28,000
20,000
30,000
78,000
11
Stock at end
(15,000)
(17,000)
(19,000)
(19,000)
12
COST OF MATS USED
13,000
18,000
28,000
59,000
13
Direct Labour
2,000
3,000
4,000
9,000
14
Commission payable
1,000
2,000
3,000
6,000
15
TOTAL VARIABLE COSTS
16,000
23,000
35,000
74,000
16
CONTRIBUTION
34,000
37,000
35,000
106,000
17
LESS OVERHEADS
18
Directors Salaries
4,000
5,000
4,000
13,000
19
Advertising
1,000
2,000
1,000
4,000
20
Wages
9,000
10,000
9,000
28,000
21
Bad debts
1,000
1,000
1,000
3,000
22
Superannuation
1,000
1,000
1,000
3,000
23
Admin. Salaries
1,000
1,000
1,000
3,000
24
Telephone & Postage
1,000
1,000
1,000
3,000
25
Travel
1,000
1,000
2,000
26
Printing & Stationery
1,000
1,000
1,000
3,000
27
Legal & Accounting
1,000
1,000
1,000
3,000
28
Sundries
1,000
1,000
1,000
3,000
29
Rent
2,000
2,000
2,000
6,000
30
Electricity & Water
1,000
1,000
1,000
3,000
31
Insurance
1,000
1,000
1,000
3,000
32
Depreciation - 10% pa
150
150
150
450
33
Lease Rentals
1,000
1,000
1,000
3,000
34
Repairs
1,000
1,000
1,000
3,000
35
General Expenses
1,000
1,000
1,000
3,000
36
Interest on Loan @ 10% pa
283
267
250
800
37
Bank Charges
1,000
1,000
1,000
3,000
38
TOTAL OVERHEADS
30,433
33,417
29,400
93,250
39
NET PROFIT
3,567
3,583
5,600
12,750
40
41
CASH FLOW
JULY
AUG
SEPT
TOTAL
42
43
INFLOW
44
Capital
100,000
-
-
100,000
45
Bank Loan
36,000
-
-
36,000
46
Debtors
50,000
50,000
47
TOTAL INFLOW
136,000
-
50,000
186,000
48
OUTFLOW
49
Direct Labour
2,000
3,000
4,000
9,000
50
Commission Payable
1,000
2,000
3,000
6,000
51
Creditors / Materials
28,000
20,000
48,000
52
Advertising
5,000
5,000
53
Wages
8,000
9,000
10,000
27,000
54
Directors Salaries
4,000
5,000
4,000
13,000
55
Superannuation
-
-
-
-
56
Admin. Salaries
1,000
1,000
2,000
57
Telephone & Postage
1,000
1,000
2,000
58
Travel
-
2,000
2,000
59
Printing & Stationery
1,000
1,000
1,000
3,000
60
Legal & Accounting
1,000
1,000
1,000
3,000
61
Sundries
1,000
1,000
1,000
3,000
62
Rent
2,000
2,000
2,000
6,000
63
Electricity & Water
-
700
700
1,400
64
Insurance
12,000
-
-
12,000
65
Expenditure on Fixed Assets
18,000
-
-
18,000
66
Lease Rentals
1,000
1,000
1,000
3,000
67
Repairs
1,000
1,000
1,000
3,000
68
General Expenses
1,000
1,000
1,000
3,000
69
Bank Charges
1,000
1,000
1,000
3,000
70
Loan Repayments - Principal
2,000
2,000
2,000
6,000
71
Loan Repayments - Interest
283
267
250
800
72
73
TOTAL OUTFLOW
61,283
62,967
54,950
179,200
74
NET CASH FLOW
74,717
(62,967)
(4,950)
6,800
75
PROGRESSIVE
74,717
11,750
6,800
6,800
76
BALANCE SHEET
JULY
AUG
SEPT
TOTAL
77
78
CURRENT ASSETS
79
Cash
74,717
11,750
6,800
80
Prepaid Advertising
4,000
2,000
1,000
81
Prepaid Insurance
11,000
10,000
9,000
82
Stock
15,000
17,000
19,000
83
Debtors
49,000
108,000
127,000
84
85
TOTAL CURRENT ASSETS
153,717
148,750
162,800
86
FIXED ASSETS
87
At Cost
18,000
18,000
18,000
88
Less Provision for Depreciation
(150)
(300)
(450)
89
90
TOTAL FIXED ASSETS
17,850
17,700
17,550
91
TOTAL ASSETS
171,567
166,450
180,350
92
93
CURRENT LIABILITIES
94
Creditors (for materials)
28,000
20,000
30,000
95
Accrued Admin. Salaries
1,000
1,000
1,000
96
Accrued Superannuation
1,000
2,000
3,000
97
Accrued Postage
1,000
1,000
1,000
98
Accrued Travel
1,000
-
-
99
Accrued Electricity & Water
1,000
1,300
1,600
100
Accrued Wages
1,000
2,000
1,000
101
102
TOTAL CURR LIABS
34,000
27,300
37,600
103
LONG-TERM LIABS
104
Long Term Loan
34,000
32,000
30,000
105
106
TOTAL L.T. LIABS
34,000
32,000
30,000
107
CAPITAL
108
CAPITAL AT BEGINNING
100,000
100,000
100,000
109
NET PROFIT
3,567
7,150
12,750
110
TOTAL CAPITAL
103,567
107,150
112,750
111
TOTAL LIABILITIES
171,567
166,450
180,350
112
PROOF
(0)
-
-
113
RATIOS
JULY
AUG
SEPT
TOTAL
114
115
CURRENT ASSETS
153,717
148,750
162,800
116
CURRENT LIABILITIES
34,000
27,300
37,600
117
WORKING CAPITAL
119,717
121,450
125,200
118
119
Current Ratio
4.52
5.45
4.33
120
121
LONG-TERM LOANS
34,000
32,000
30,000
122
CAPITAL
103,567
107,150
112,750
123
124
Debt / Equity (Gearing)
0.33
0.30
0.27
125
126
NET PROFIT (A/TAX)-CUM
3,567
7,150
12,750
127
CAPITAL
103,567
107,150
112,750
128
R.O.I -Pa (Cumulative)
41%
40%
45%
the row numbers and column letters have been included in the spreadsheet to facilitate the learning activity which is to follow
as a learning activity to practice spreadsheet modelling techniques, each learner is now required to establish their own spreadsheet for Basic Enterprises
the spreadsheet for Basic Enterprises was done using Microsoft Excel but can be done on any spreadsheet software (preferably a Windows based package for quality of presentation)
after you have completed your model you can use it as a starter for any other model you wish to create. In other words, having done it once you can use it time and again in all sorts of situations, refining it and improving it as you go
to create your model you have to enter each cell in accordance with the instructions and notes in the spreadsheet headed “Basic Enterprises - Notes” which follows on the next page
refer also to the following notes before you start
the spreadsheet model is based on “double-entry”. That is, each entry will have a single corresponding entry in a formula or total
this model has been simplified and does not include a “front-end” for assumptions / variables or a funds statement
whenever you are doing a model like this and it doesn’t balance (ie total assets will not equal total liabilities the usual errors are one-sided entries (no corresponding double entry) and incorrect formulae (including incorrect cell or range references)
the best way to make sure that the model is in balance is to make sure the “proof” row is zero at all times (try putting that row in a horizontal split window)
when you do this exercise do not just copy the text and formulae row by row at the same time. You won’t learn as much as following the preferred steps set out below.
Preferred steps
enter all text rows in Column B
enter all formulae for rows marked “Total or Calculation”, first into Column C and then copied into Columns D, E and F where appropriate
cross off or tick each cell on your “Notes” sheet as you go
start entering Column C as follows
start at the top with Income on row 6 and enter the amount of 50,000 (cross it off) and then the formula in the row referred to in the “Double Entry Notes” column .....Row 83........Cross it off by putting a line through that part of the formula shown on your “Notes” sheet.
notice how your Net Profit line (Row 39) is 50,000 and the balance sheet row for Accumulated Profit (Row 109) is also 50,000
notice also that Total Assets (Row 91) is now also 50,000 and Total Assets equal Total Liabilities (Row 111)
notice also that the proof in Row 112 is zero, proving that your model and your double entry system is working (remember the tip about putting this Proof row in a separate window?)
go on down the page for each row. In some cases you will be putting in only part of the formula. That’s OK, you can edit the formula later to add in the next part when you come to it. The most important thing is to make sure each entry you make has a corresponding double entry and that the proof is zero
when you get to rows 32 & 36 you may prefer to wait and put these formulae in last of all after you have entered the appropriate formulae in the balance sheet. The formulae in rows 32 & 36 depend on other entries for them to show a result.
if the proof is not zero then you are into problem-solving mode. Check your double entries, cell references, range references, calculation formulae etc
notice how all the Profit & Loss statement items are reflected in the balance sheet by the figure in Row 109
notice how all the Cash Flow statement items are reflected in the balance sheet by the figure in Row 79
now compare your spreadsheet results for Column C with the spreadsheet “Basic Enterprises - Solution”
start on Column D
start with the balance sheet rows and copy formulae from Column C to Column D
edit each formulae (except Cash at Bank/Row 79 because this is already a progressive amount) to pick up the cell reference for the previous period’s balance
copy all balance sheet formulae into Columns E & F
copy relevant Profit and Loss and Cash Flow formulae from Column C into Columns D, E & F
enter all amounts shown in Column C for P&L and cash flow rows (tick them off as you go)
notice the technique for debtors and creditors in the cash flow which assumes a 30 day lag in payment. If the lag was 60 days then Column E would pick up figures from Column C income and purchases. Make sure the debtors and creditors formulae in the balance sheet have picked up these cash flow items.
make sure of your zero proof
now compare your spreadsheet results for Column D with the spreadsheet “Basic Enterprises - Solution”
start on Column E
enter all amounts shown in Column E for P&L and cash flow rows (tick them off as you go)
make sure of your zero proof
now compare your spreadsheet results for Column E with the spreadsheet “Basic Enterprises - Solution”
start on Column F
complete the cross-add totals where appropriate (tick them off as you go)
notice there is no need to cross-add the balance sheet items (this would produce nonsense figures)
make sure of your zero proof
now compare your spreadsheet results for Column F with the spreadsheet “Basic Enterprises - Solution” (see part 2)
Copyright © Bill Wright 1994
